USE [NANS21V]
GO

/****** Object:  StoredProcedure [dbo].[SyozokuBetsuPlayerList]    Script Date: 08/01/2023 08:39:02 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SyozokuBetsuPlayerList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SyozokuBetsuPlayerList]
GO

CREATE PROCEDURE [dbo].[SyozokuBetsuPlayerList]
	@TAIKAINO int
AS
SELECT KS.TAIKAINO,
       KS.SYOZOKUNO1,
       KS.KYOGISYAFLG,
       S.SYOZOKUMEI,
       KS.KYOGISYANO, 
	   (KS.NUMBER1 + CONVERT(varchar, KS.NUMBER2)) AS NUMBER, 
       KS.KYOGISYAMEI,
       KS.KYOGISYAKANA,
       KS.SEINEN, 
       KS.GAKUNEN,
       dbo.Seibetu(KG.SEIBETUNO) AS SEIBETU,
       KG.KYOGIMEI + '　' + TRO.ROUNDMEI AS KYOGIMEI,
       LA.KUMINO AS KUMINO,
       TRO.HIDUKE,
       (CONVERT(varchar, KG.TAIKAINO) + '-' + 
        CONVERT(varchar, KG.KYOGINO) + '-' + 
        CONVERT(varchar, TRO.ROUNDNO) + '-' + 
        CONVERT(varchar, TRO.SUBNO) + '-' + 
        CONVERT(varchar, TRO.KUMISUU) + '-' + 
        CONVERT(varchar, KG.SYUMOKUKUBUN) + '-' + 
        CONVERT(varchar, KG.SUBSYUMOKUKUBUN) + '-' + 
        CONVERT(varchar, KG.GATTAIFLG) + '-' +
        CONVERT(varchar,KG.SUBKIROKUKUBUN)
       ) AS KEKKAFLG,
       (
            CASE WHEN KG.SYUMOKUKUBUN=8
            THEN
                dbo.Kiroku(RE.KIROKU,KG.SUBSYUMOKUKUBUN,KG.SUBKIROKUKUBUN,(CASE WHEN ISNUMERIC(RE.EXTEND2) = 1 THEN RE.EXTEND2 ELSE 3 END))
            ELSE
                dbo.Kiroku(RE.KIROKU,KG.SYUMOKUKUBUN,KG.KIROKUKUBUN,(CASE WHEN ISNUMERIC(RE.EXTEND2) = 1 THEN RE.EXTEND2 ELSE 3 END))
            END
       ) AS KIROKU,
       CONVERT(int, NULLIF(RE.EXTEND3, '')) AS TOKUTEN,
       dbo.Jyuni(RE.JYUNI,SK.OPENFLG) AS JYUNI,
       TIR.JYUNI AS TIMERACE_JYUNI,
       RE.EXTEND1 AS KAZE,
       (dbo.CommentCodeConvert(RE.RCOMMENT,LA.SYUKKETU)) AS RCOMMENT,
	   KSE.EXTEND7 AS KSE_EXTEND7
FROM TBL_KYOGISYA KS
		LEFT JOIN TBL_KYOGISYA_EXTE AS KSE
		ON KS.TAIKAINO = KSE.TAIKAINO
		AND KS.KYOGISYANO = KSE.KYOGISYANO
    ,TBL_SANKAKYOGI SK
        LEFT JOIN TBL_KONSEI_SYUKEI AS KSK
            ON SK.TAIKAINO = KSK.TAIKAINO
            AND SK.KYOGINO = KSK.KYOGINO
            AND SK.KYOGISYANO = KSK.KYOGISYANO
        LEFT JOIN TBL_TIMERACE AS TIR
            ON SK.TAIKAINO = TIR.TAIKAINO
            AND SK.KYOGINO = TIR.KYOGINO
            AND SK.KYOGISYANO = TIR.SENSYUNO
    ,TBL_LANE LA
        LEFT JOIN TBL_RESULT AS RE 
			ON LA.TAIKAINO = RE.TAIKAINO
			AND LA.KYOGINO = RE.KYOGINO
			AND LA.SUBNO = RE.SUBNO
			AND LA.ROUNDNO = RE.ROUNDNO
			AND LA.KUMINO = RE.KUMINO
			AND LA.LANE = RE.LANE
    ,TBL_ROUND TRO
    ,TBL_SYOZOKU AS S
    ,(SELECT TK.TAIKAINO, 
           TK.KYOGINO, 
           TK.KYOGIMEI + SUB.SUBMEI AS KYOGIMEI, 
           TK.KYOGIKANA, 
           TK.SEIBETUNO, 
           TSM.SYUMOKUNO, 
           TSM.SYUMOKUMEI, 
           TSM.KIROKUKUBUN, 
           TSM.SYUMOKUKUBUN,
           SUB.SUBMEI, 
           SUB.SUBNO, 
           SUB.KIROKUKUBUN AS SUBKIROKUKUBUN, 
           SUB.SYUMOKUKUBUN AS SUBSYUMOKUKUBUN, 
           TS.PRINTNO AS SYUBETU_PRINTNO, 
           TSM.PRINTNO AS SYUMOKU_PRINTNO, 
           SUB.KONSEIID, 
           TK.GATTAIFLG
     FROM TBL_KYOGI AS TK, 
          TBL_SYUBETU AS TS, 
          TBL_SYUMOKU_MASTER AS TSM, 
          (SELECT K.TAIKAINO, 
                  K.KYOGINO, 
                  K.SYUMOKUNO, 
                  K.KYOGIMEI, 
                  K.KYOGIKANA, 
                  0 AS SUBNO, 
                  '' AS SUBMEI,
                  K.SEIBETUNO, 
                  SM.KIROKUKUBUN, 
                  SM.SYUMOKUKUBUN, 
                  SM.PRINTNO, 
                  0 AS KONSEIID,
                  0 AS KONSEINO
            FROM TBL_KYOGI AS K, 
                 TBL_SYUMOKU_MASTER AS SM
              WHERE K.SYUMOKUNO = SM.SYUMOKUNO
                AND (SM.KONSEISYUMOKUSUU = 0 OR SM.KONSEISYUMOKUSUU IS NULL)
            UNION
              SELECT K.TAIKAINO, 
                     K.KYOGINO, 
                     K.SYUMOKUNO, 
                     K.KYOGIMEI, 
                     K.KYOGIKANA, 
                     SM.SYUMOKUNO AS SUBNO, 
                     SM.SYUMOKUMEI AS SUBMEI,
                     K.SEIBETUNO, 
                     SM.KIROKUKUBUN, 
                     SM.SYUMOKUKUBUN, 
                     SM.PRINTNO, 
                     KM.ID AS KONSEIID
                     ,KM.KONSEINO AS KONSEINO
                FROM TBL_KYOGI AS K, 
                     TBL_SYUMOKU_MASTER AS SM, 
                     TBL_KONSEI_MASTER AS KM
                WHERE K.SYUMOKUNO = KM.SYUMOKUNO
                  AND KM.KONSEINO = SM.SYUMOKUNO
          ) AS SUB
     WHERE TK.TAIKAINO = SUB.TAIKAINO
       AND TK.SYUMOKUNO = SUB.SYUMOKUNO
       AND TK.SYUMOKUNO = TSM.SYUMOKUNO
       AND TK.KYOGINO = SUB.KYOGINO
       AND TS.SYUBETUNO = TK.SYUBETUNO
       AND (TK.GATTAIFLG = 0 OR TK.GATTAIFLG = 2)
    ) KG
WHERE KS.TAIKAINO = SK.TAIKAINO
  AND KS.KYOGISYANO = SK.KYOGISYANO
  AND KS.TAIKAINO = LA.TAIKAINO
  AND KS.KYOGISYANO = LA.KYOGISYANO
  AND KS.TAIKAINO = S.TAIKAINO
  AND KS.SYOZOKUNO1 = S.SYOZOKUNO
  AND SK.KYOGINO = LA.KYOGINO
  AND LA.TAIKAINO = TRO.TAIKAINO
  AND LA.KYOGINO = TRO.KYOGINO
  AND LA.SUBNO = TRO.SUBNO
  AND LA.ROUNDNO = TRO.ROUNDNO
  AND LA.TAIKAINO = KG.TAIKAINO
  AND LA.KYOGINO = KG.KYOGINO
  AND LA.SUBNO = KG.SUBNO
  AND KS.TAIKAINO = @TAIKAINO
ORDER BY KS.TAIKAINO, KS.SYOZOKUNO1, KS.KYOGISYAFLG, KG.SEIBETUNO, KS.KYOGISYANO, KG.SYUBETU_PRINTNO, KG.SYUMOKU_PRINTNO, TRO.ROUNDNO